package beanstao.util.office.excel;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import beanstao.util.Sber;
import beanstao.util.io.FileHelper;
import beanstao.util.log.LogHelper;

/**
 * Excel操作类。 本类提供了简单的获取指定cell给指定cell添加内容的方法。<br>
 * http://poi.apache.org/<br>
 * Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程式对Microsoft Office格式档案读和写的功能。<br>
 * 结构：<br>
 * HSSF － 提供读写Microsoft Excel格式档案的功能。<br>
 * XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。<br>
 * HWPF － 提供读写Microsoft Word格式档案的功能。<br>
 * HSLF － 提供读写Microsoft PowerPoint格式档案的功能。<br>
 * HDGF － 提供读写Microsoft Visio格式档案的功能。<br>
 * 
 * @param <T>
 *            数据源
 * @author beanstao@google.com 
 */
public abstract class AbstractExcelFile<T> implements IExcelFile<T>
{
	protected String _BasePath;
	protected String _FileSubPath;
	protected File _CurrFile;
	protected HSSFWorkbook workbook = new HSSFWorkbook();

	/**
	 * @return 当前实例面向的Excel文件
	 */
	public File getCurrFile()
	{
		return _CurrFile;
	}

	/**
	 * @return 返回网站子路径
	 */
	public String getWebPath()
	{
		return _FileSubPath.replace("\\", "/");
	}

	/**
	 * 创建文件，并返回该文件的输出流，当异常时，返回为空。
	 * 
	 * @param basePath
	 * @return
	 */
	protected HSSFWorkbook createFile(String basePath)
	{
		_FileSubPath = ExcelSetting.ME().excelFilePath().getSubPath();
		String fullPath = Sber.ME(basePath).append(_FileSubPath).toString().replace(File.separator + File.separator, File.separator);
		_CurrFile = new File(fullPath);

		try
		{
			this.logDebug("准备生成Excel文件:" + _CurrFile.getPath());
			FileHelper.createNewFile(_CurrFile);
		}
		catch (IOException e)
		{
			this.logError("创建Excel文件失败。异常。" + fullPath, e);
		}
		return workbook;
	}

	/**
	 * 将HSSFWorkbook中的内容向实体文件写入。IO操作。
	 */
	protected void save()
	{
		FileOutputStream fileOut = null;
		try
		{
			fileOut = new FileOutputStream(_CurrFile);
		}
		catch (FileNotFoundException e)
		{
			this.logWarn(_CurrFile.getPath() + "不存在。", e);
		}
		try
		{
			workbook.write(fileOut);
		}
		catch (IOException e)
		{
			this.logWarn("HSSFWorkbook向文件流中写入发生错误。" + _CurrFile.getPath(), e);
		}
		try
		{
			fileOut.close();
		}
		catch (IOException e)
		{
			this.logWarn("EXcel文件流关闭异常。" + _CurrFile.getPath(), e);
		}
		this.logDebug(_CurrFile.getPath() + "创建，填充，保存成功。");
	}

	// =针对HSSFWorkbook的一些助手方法=======================

	protected final static SimpleDateFormat fullTimeFmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	protected final static SimpleDateFormat fullDateFmt = new SimpleDateFormat("yyyy-MM-dd");

	/**
	 * 给Excel中的某个sheet的某个单元格赋值。
	 * 
	 * @param cellPositionStr
	 *            位置参数如A12表示A列，12行。
	 * @param sheetNo
	 * @param v
	 * @return
	 */
	public HSSFCell setCellValue(String cellPositionStr, int sheetNo, Object v)
	{
		Point p = new Point(cellPositionStr);
		return this.setCellValue(p, sheetNo, v, false);
	}

	public HSSFCell setCellValue(String cellPositionStr, Object v)
	{
		Point p = new Point(cellPositionStr);
		return this.setCellValue(p, 0, v, false);
	}

	// jert
	public HSSFCell setCellValue(String cellPositionStr, int sheetNo, Object v, boolean ishidden)
	{
		Point p = new Point(cellPositionStr);
		return this.setCellValue(p, sheetNo, v, ishidden);
	}

	public HSSFCell setCellValue(String cellPositionStr, Object v, boolean ishidden)
	{
		Point p = new Point(cellPositionStr);
		return this.setCellValue(p, 0, v, ishidden);
	}

	/**
	 * 给Excel中的某个sheet的某个单元格赋值。
	 * 
	 * @param colNumStr
	 *            哪一列
	 * @param rowNum
	 * @param sheetNo
	 * @param v
	 * @return
	 */
	public HSSFCell setCellValue(String colNumStr, int rowNum, int sheetNo, Object v)
	{
		Point p = new Point(colNumStr, rowNum);
		return this.setCellValue(p, sheetNo, v, false);
	}

	// jert
	public HSSFCell setCellValue(String colNumStr, int rowNum, int sheetNo, Object v, boolean ishidden)
	{
		Point p = new Point(colNumStr, rowNum);
		return this.setCellValue(p, sheetNo, v, ishidden);
	}

	public HSSFCell setCellValue(Point p, int sheetNo, Object v, boolean isHidden)
	{
		return this.setCellValue(p.col, p.row, sheetNo, v, isHidden);
	}

	/**
	 * 给Excel中的某个sheet的某个单元格赋值。
	 * 
	 * @param colNum
	 * @param rowNum
	 *            从0开始。
	 * @param sheetNo
	 *            从0开始。
	 * @param v
	 * @return
	 */
	public HSSFCell setCellValue(int colNum, int rowNum, int sheetNo, Object v, boolean isHidden)
	{
		HSSFCell cell;
		if (isHidden)
			cell = this.getCell(colNum, rowNum, sheetNo, isHidden);
		else
			cell = this.getCell(colNum, rowNum, sheetNo);
		if (v == null)
		{
			cell.setCellValue(new HSSFRichTextString(""));// TODO 添加的值是以单元格格式为准，还是以数据类型为准？
			return cell;
		}
		if (v.getClass() == Boolean.class)
			cell.setCellValue((Boolean) v);
		else if (v.getClass() == Integer.class)
			cell.setCellValue((Integer) v);
		else if (v.getClass() == Double.class)
			cell.setCellValue((Double) v);
		else if (v.getClass() == Float.class)
			cell.setCellValue((Float) v);
		else if (v.getClass() == BigDecimal.class)
			cell.setCellValue(((BigDecimal) v).doubleValue());
		else if (v.getClass() == Date.class)
			cell.setCellValue(new HSSFRichTextString(fullTimeFmt.format((Date) v)));// TODO 权益之计
		else if (v.getClass() == String.class)
			cell.setCellValue(new HSSFRichTextString((String) v));
		else
			cell.setCellValue(new HSSFRichTextString(v.toString()));
		return cell;
	}

	/**
	 * 根据指定行列和sheet获取单元。
	 * 
	 * @param rowNum
	 * @param cellNum
	 * @param sheetNo
	 * @return
	 */
	public HSSFCell getCell(int colNum, int rowNum, int sheetNo)
	{
		HSSFRow row = this.getRow(rowNum, sheetNo);
		HSSFCell cell = row.getCell(colNum);
		if (cell == null)
			cell = row.createCell(colNum);
		return cell;
	}

	public HSSFCell getCell(String colNumStr, int rowNum, int sheetNo)
	{
		int colNum = cellNumStr2Int(colNumStr);
		return this.getCell(colNum, rowNum, sheetNo);
	}

	public HSSFCell getCell(String cellPositionStr, int sheetNo)
	{
		Point p = new Point(cellPositionStr);
		return this.getCell(p.col, p.row, sheetNo);
	}

	/**
	 * 获取一个隐藏Cell
	 * 
	 * @param colNum
	 * @param rowNum
	 * @param sheetNo
	 * @param isHidden
	 * @return
	 * @see jert
	 */
	public HSSFCell getCell(int colNum, int rowNum, int sheetNo, boolean isHidden)
	{
		HSSFRow row = this.getRow(rowNum, sheetNo, colNum, isHidden);
		HSSFCell cell = row.getCell(colNum);
		if (cell == null)
			cell = row.createCell(colNum);
		return cell;
	}

	/**
	 * 获取某一行。
	 * 
	 * @param rowNum
	 * @param sheetNo
	 * @return
	 */
	public HSSFRow getRow(int rowNum, int sheetNo)
	{
		HSSFSheet sheet = null;
		if (sheetNo >= workbook.getNumberOfSheets())
			sheet = workbook.createSheet("sheet-" + sheetNo);
		else
			sheet = workbook.getSheetAt(sheetNo);
		HSSFRow row = sheet.getRow(rowNum - 1);
		if (row == null)
			row = sheet.createRow(rowNum - 1);
		return row;
	}

	/**
	 * 获取某一行，其中可以指定隐藏列。
	 * 
	 * @param rowNum
	 * @param sheetNo
	 * @param rowNum
	 * @param isHidden
	 * @return
	 * @see jert
	 */
	public HSSFRow getRow(int rowNum, int sheetNo, int colNum, boolean isHidden)
	{
		HSSFSheet sheet = null;
		if (sheetNo >= workbook.getNumberOfSheets())
			sheet = workbook.createSheet("sheet-" + sheetNo);
		else
			sheet = workbook.getSheetAt(sheetNo);
		HSSFRow row = sheet.getRow(rowNum - 1);
		if (row == null)
			row = sheet.createRow(rowNum - 1);
		if (isHidden)
			sheet.setColumnHidden(colNum, isHidden);
		return row;
	}

	/**
	 * 将列的名称转换为数字。
	 * 
	 * @param cellNumStr
	 * @return
	 */
	private static int cellNumStr2Int(String cellNumStr)
	{
		cellNumStr = cellNumStr.toLowerCase();
		int cellNum = 0;
		char[] chars = cellNumStr.toCharArray();
		int j = 0;
		for (int i = chars.length - 1; i >= 0; i--)
		{
			cellNum += (chars[i] - 'a' + 1) * Math.pow(26, j);
			j++;
		}
		return cellNum - 1;
	}

	public static String cellNumIntToStr(int colNum)
	{
		String colName = "";
		do
		{
			char c = (char) (colNum % 26 + 'A');
			colName = c + colName;
			colNum = colNum / 26 - 1;
		} while (colNum >= 0);
		return colName;
	}

	// = 日志接口(ILog)的实现 ============

	@Override
	public void logDebug(Object message)
	{
		LogHelper.logDebug(this, message);
	}

	@Override
	public void logDebug(Object message, Throwable t)
	{
		LogHelper.logDebug(this, message, t);
	}

	@Override
	public void logError(Object message)
	{
		LogHelper.logError(this, message);
	}

	@Override
	public void logError(Object message, Throwable t)
	{
		LogHelper.logError(this, message, t);
	}

	@Override
	public void logInfo(Object message)
	{
		LogHelper.logInfo(this, message);
	}

	@Override
	public void logInfo(Object message, Throwable t)
	{
		LogHelper.logInfo(this, message, t);
	}

	@Override
	public void logWarn(Object message)
	{
		LogHelper.logWarn(this, message);
	}

	@Override
	public void logWarn(Object message, Throwable t)
	{
		LogHelper.logWarn(this, message, t);
	}

	// =====================

	class Point
	{
		public Point(String cellPositionStr)
		{
			char[] chars = cellPositionStr.toCharArray();
			int i = 0;
			for (; i < chars.length; i++)
				if (Character.isDigit(chars[i]))
					break;
			row = Integer.parseInt(cellPositionStr.substring(i));
			col = cellNumStr2Int(cellPositionStr.substring(0, i));
		}

		public Point(String colStr, int row)
		{
			col = cellNumStr2Int(colStr);
			this.row = row;
		}

		int row;
		int col;
	}

}
